<?php

/**
 * @author ducnhat
 * @email ddnhat@gmail.com
 * @copyright 2014
 */

class Admin_Model_Log extends Zend_Db_Table{
    public function saveData($data){
    	$db = Zend_Registry::get('db');
        
        $db->beginTransaction();
        
        try{
            if(isset($data['id']) && $this->checkExists($data['id'])){
                $id = $data['id'];
                
                $db->update("log", $data, "id = $id");
            }else{
                $db->insert('log', $data);
            }
            
            $db->commit();
            
            return true;
        }catch(Exception $e){
            $db->rollBack();
            
            return false;
        }
    }
    
    public function getDatas($dateFrom = null, $dateTo = null){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_ASSOC);
        
        $result = array();
        
        $select = $db->select()
                    ->from(array('l' => 'log'), array('date'))
                    ->group('l.date')
                    ->order('l.date DESC')
                    ->order('l.id');
        
        $selectDateOrderBuy = $db->select()
                    ->distinct(true)
                    ->from(array('ob' => 'order_buys'), array('DATE_FORMAT(ob.date, "%Y-%m-%d") as date'))
                    ->group('date')
                    ->order('ob.date DESC')
                    ->order('ob.id');
        
        if($dateFrom != null){
            $date = DateTime::createFromFormat('d/m/Y', $dateFrom);
            $select->where("l.date >= ?", $date->format("Y-m-d"));
            $selectDateOrderBuy->where("ob.date >= ?", $date->format("Y-m-d"));
        }else{
            $date = date("Y-m-d", strtotime("-1 week"));
            $select->where("l.date >= ?", $date);
        }
        
        if($dateTo != null){
            $date = DateTime::createFromFormat('d/m/Y', $dateTo);
            $select->where("l.date <= ?", $date->format("Y-m-d"));
            $selectDateOrderBuy->where("ob.date <= ?", $date->format("Y-m-d"));
        }
        
        $dateDateOrderBuy = $db->fetchAll($selectDateOrderBuy);
        $date = $db->fetchAll($select);
        
        foreach($date as $d){
            $d = current($d);
            
            $selectBuy = $db->select()
                    ->from(array('l' => 'log'), array('id', 'total'))
                    ->join(array('c' => 'customers'), "l.name = c.id", array('name'))
                    ->where('type = ?', 0)
                    ->where('l.`date` = ?', $d);
            
            $result[$d]['buy'] = $db->fetchAll($selectBuy);
            
            $selectSumBuy = $db->select()
                    ->from(array('l' => 'log'), array('SUM(total) as total'))
                    ->where('type = ?', 0)
                    ->where('l.`date` = ?', $d);
            
            $result[$d]['totalbuy'] = $db->fetchAll($selectSumBuy);
            
            $selectPayment = $db->select()
                    ->from(array('l' => 'log'), array('id', 'total', 'receiver'))
                    ->join(array('c' => 'customers'), "l.name = c.id", array('name'))
                    ->where('type = ?', 2)
                    ->where('l.`date` = ?', $d);
            
            $result[$d]['payment'] = $db->fetchAll($selectPayment);
            
            $selectSumPayment = $db->select()
                    ->from(array('l' => 'log'), array('SUM(total) as total'))
                    ->where('type = ?', 2)
                    ->where('l.`date` = ?', $d);
            
            $result[$d]['totalpayment'] = $db->fetchAll($selectSumPayment);
            
            $selectSale = $db->select()
                    ->from(array('l' => 'log'), array('id', 'total'))
                    ->join(array('s' => 'suppliers'), "l.name = s.id", array('name'))
                    ->where('l.`type` = 1')
                    ->where('l.`date` = ?', $d);
            
            $result[$d]['sale'] = $db->fetchAll($selectSale);
            
            $selectSumSale = $db->select()
                    ->from(array('l' => 'log'), array('SUM(total) as total'))
                    ->where('type = ?', 1)
                    ->where('l.`date` = ?', $d);
            
            $result[$d]['totalsale'] = $db->fetchAll($selectSumSale);
            
            $selectOrderBuys = $db->select()
                    ->from(array('ob' => 'order_buys'), array('subtotal as total'))
                    ->join(array('s' => 'suppliers'), "ob.supplier_id = s.id", array('name'))
                    ->where('DATE_FORMAT(ob.`date`, "%Y-%m-%d") = ?', $d);
                    
            $result[$d]['order_buys'] = $db->fetchAll($selectOrderBuys);
        }
        
        return $result;
    }
    
    public function getTotalBuy(){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_ASSOC);
        
        $result = array();
        
        $select = $db->select()
                    ->from(array('l' => 'log'), array('SUM(total) as totalBuy'))
                    ->where('l.type = 0');
        
        $result = $db->fetchRow($select);
        
        return $result;
    }
    
    public function getTotalSale(){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_ASSOC);
        
        $result = array();
        
        $select = $db->select()
                    ->from(array('l' => 'log'), array('SUM(total) as totalSale'))
                    ->where('l.type = 1');
        
        $result = $db->fetchRow($select);
        
        return $result;
    }
    
    public function getTotalOrderBuys(){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_ASSOC);
        
        $result = array();
        
        $select = $db->select()
                    ->from(array('ob' => 'order_buys'), array('SUM(subtotal) as totalOrder'));
        
        $result = $db->fetchRow($select);
        
        return $result;
    }
    
    public function getTotalPayment(){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_ASSOC);
        
        $result = array();
        
        $select = $db->select()
                    ->from(array('l' => 'log'), array('SUM(total) as totalPayment'))
                    ->where('l.type = 2');
        
        $result = $db->fetchRow($select);
        
        return $result;
    }
    
    public function deleteData($id){
        $db = Zend_Registry::get('db');
        
        $db->beginTransaction();
        
        try{
            $db->delete("log", "id = $id");
            $db->commit();
            
            return true;
        }catch(Exception $e){
            $db->rollBack();
            
            return false;
        }
    }
}